<?php
/**
 * I am a REST MySQL Database Manager Service the structure for the url is as follows:
 * 
 * @eample
 * <code>
 * url string:
 * 
 * http://localhost/service.php?
 * h=HOST
 * &u=USERNAME
 * &p=PASSWORD
 * &m=MODE
 * &d=DATABASE
 * &t=TABLE
 * &q=QUERY
 * </code>
 * 
 * <code>
 * result:
 * 
 * [
 * 		   {
 * 		      "Database":"information_schema"
 * 		   },
 * 		   {
 * 		      "Database":"mysql"
 * 		   },
 * 		   {
 * 		      "Database":"test"
 * 		   }
 * 	]
 * </code>
 * 
 * TABLE OF CONTENTS
 * 
 * 1. MYSQL SHOW METHODS
 * 2. PRIVATE DATABASE/TABLE METHODS
 * 3. QUERY BUILDER/CREATE/UPDATE/DELETE METHODS
 * 4. ANALYZE/CHECK/OPTIMIZE/REPAIR METHODS
 * 5. BACKUP/IMPORT/EXPORT METHODS
 * 6. SERVER VARIABLES
 * 7. DATA METHODS
 * 8. UTILITY METHODS
 * 9. CLASS TESTING
 * 
 *
 * @name MySQLService
 * @author  Jonnie Spratley
 * @version 1.0
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 */
class MySQLService
{
	/**
	 * I am the database link
	 *
	 * @var private
	 */
	private $dbconn;
	private $query_pieces = array();

	/**
	 * I hold alot of access to monitor and manager mysql tables
	 *
	 * @param [string] $host Host name
	 * @param [string] $username User name
	 * @param [string] $password User password
	 * 
	 * @return MySQLService
	 */
	public function MySQLService( $host, $username, $password )
	{
		//temporary for the bs warning signs on live
		// Report simple running errors
		error_reporting ( E_ERROR | E_USER_ERROR | E_PARSE );
		
		$this->dbconn = new mysqli ( $host, $username, $password );
		/* check connection */
		if ( mysqli_connect_errno () )
		{
			trigger_error ( 'Database connection failure: Username/Password was incorrect.', E_USER_ERROR );
			exit ();
		} else
		{
			return 'true';
		}
	}

	/* ********************************************************************
 * ********************************************************************
 *  
 * 			 				1. MYSQL SHOW METHODS
 *
 * Below is all the methods for getting tables, columns, databases,
 * indexs, statusus from the database.
 * 
 * SHOW DATABASES;
 * SHOW TABLES FROM test;
 * SHOW TABLE STATUS LIKE 'users';
 * SHOW INDEX FROM 'contacts';
 * SHOW INDEX FROM contacts;
 * SHOW COLUMNS FROM contacts;
 * SHOW STATUS FROM test;
 * SHOW TABLE STATUS FROM test;
 * 
 * ********************************************************************
 * *********************************************************************/
	
	/**
	 * I show all of the columns for a specified table.
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json] Json of all the columns
	 */
	public function getTableColumns( $whatDatabase, $whatTable )
	{
		$sql = "SHOW COLUMNS FROM $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I get all tables in database
	 *
	 * @param [string] $database the database
	 * @return [json]
	 */
	public function showTableStatus( $whatDatabase )
	{
		$sql = mysqli_query ( $this->dbconn, "SHOW TABLE STATUS FROM $whatDatabase" );
		$tables = array();
		
		while ( $row = mysqli_fetch_assoc ( $sql ) )
		{
			$tables[] = $row;
		}
		return json_encode ( $tables );
	}

	/**
	 * I get the primary key for the table.
	 *
	 * @param [string] $database the database
	 * @param [string] $table the table
	 * @return [json]
	 */
	public function getTableIndex( $whatDatabase, $whatTable )
	{
		$sql = "SHOW INDEX FROM $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I get all databases, tables, columns, and fields in the database.
	 * Formatted specially for Flex's Tree control.
	 *
	 * @return [json]
	 */
	public function getDatabasesAndTables()
	{
		//Database query     
		$databaseSQL = mysqli_query ( $this->dbconn, "SHOW DATABASES" );
		
		//New database array
		$databases = array();
		
		//Loop the query
		while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
		{
			//Create a new array of tables for each database
			$tables = array();
			
			foreach ( $database as $key => $value )
			{
				//Set the table array to get the tbles from the database 
				$tables = $this->_getTables ( $value );
			}
			
			//Add the tables to the database array
			$databases[] = array( 
				"aDatabase" => $value, "aType" => "database", "aData" => $key, "aIcon" => "databaseIcon", "aTables" => $tables 
			);
		}
		sort ( $databases );
		
		//Encode in json
		return json_encode ( $databases );
	}

	/**
	 * I get all the databases
	 *
	 * @return [json]
	 */
	public function getDatabases()
	{
		//Database query     
		$databaseSQL = mysqli_query ( $this->dbconn, "SHOW DATABASES" );
		
		//New database array
		$databases = array();
		
		//Loop the query
		while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
		{
			//Create a new array of tables for each database
			$tables = array();
			$status = array();
			foreach ( $database as $key => $value )
			{
				//Set the table array to get the tbles from the database 
				$tables = $this->_getTables ( $value );
				$status = $this->_getTableStatus ( $value );
			}
			//Add the tables to the database array
			$databases[] = array( 
				"aDatabase" => $value, "aType" => "database", "aData" => $key, "aIcon" => "databaseIcon", "aTables" => $tables, "aStatus" => $status 
			);
		}
		sort ( $databases );
		
		//Encode in json
		return json_encode ( $databases );
		//return $databases;
	}

	/**
	 * I get all tables in the database
	 *
	 * @param [string] $whatDatabase the name of the database
	 * @return [json]
	 */
	public function getTables( $whatDatabase )
	{
		//table query
		$tableSQL = mysqli_query ( $this->dbconn, "SHOW TABLES FROM $whatDatabase" );
		
		//create a new array of tables
		$tables = array();
		
		//loop all the results
		while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
		{
			$fields = array();
			//for each table in the result make an array
			foreach ( $table as $key => $value )
			{
				//set the field array to the fiesl from the fnction
				//$tables[] = array( "label" => $value );
				$fields = $this->_describeTable ( $whatDatabase, $value );
			}
			$tables[] = array( 
				"tableName" => $value, "fields" => $fields 
			);
		}
		
		//return $this->_queryToJSON( $indexSQL );
		return json_encode ( $tables );
		//return $tables;
	}

	/**
	 * I describe a table
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function describeTable( $whatDatabase, $whatTable )
	{
		$sql = mysqli_query ( $this->dbconn, "DESCRIBE $whatDatabase.$whatTable" );
		$tables = array();
		
		while ( $row = mysqli_fetch_assoc ( $sql ) )
		{
			$tables[] = array( 
				'Field' => $row[ 'Field' ], 'Type' => $row[ 'Type' ], 'Null' => $row[ 'Null' ], 'Default' => $row[ 'Default' ], 'Extra' => $row[ 'Extra' ], 'Key' => $row[ 'Key' ] 
			);
		}
		sort ( $tables );
		
		return json_encode ( $tables );
	}

	/**
	 * I get user information
	 *
	 * @param [string] $username the users name
	 * @return [json]
	 */
	public function getUserInfo( $username )
	{
		$sql = "SELECT * FROM mysql.user_info WHERE User = '$username'";
		$result = mysqli_query ( $this->dbconn, $sql );
		//return $this->_queryToJSON($sql);
		$array = array();
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			$array[] = array( 
				"User" => $row[ 'User' ], 
					/*"Fullname" => $row[ 'Fullname' ],*/ 
					"Description" => $row[ 'Description' ], "Icon" => base64_encode ( $row[ 'Icon' ] ), "Email" => $row[ 'Email' ], "Info" => $row[ 'Contact_information' ] 
			);
		}
		return json_encode ( $array );
	}

	/* ********************************************************************
 * ********************************************************************
 * 
 * 						2. PRIVATE DATABASE/TABLE METHODS
 * 
 * Below is all the private methods that build up the database 
 * and table tree with information about each item. 
 * 
 * Example:
 * 
 * DatabaseName/
 * 		TableName/
 * 			FieldName/
 * 
 * ********************************************************************
 * ********************************************************************
	
	/**
	 * I get all tables for a database
	 *
	 * @param [string] $whatDatabase the database
	 * @return [array]
	 */
	public function _getTables( $whatDatabase )
	{
		//table query
		$tableSQL = mysqli_query ( $this->dbconn, "SHOW TABLES FROM $whatDatabase" );
		
		//create a new array of tables
		$tables = array();
		
		//loop all the results
		while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
		{
			$fields = array();
			//$status = array();
			

			//for each table in the result make an array
			foreach ( $table as $t_key => $t_value )
			{
				//get the tables fields for each table
				$fields = $this->_describeTable ( $whatDatabase, $t_value );
				$primaryKey = $this->_getTableKey ( $whatDatabase, $t_value );
				
			//now get the status for that table
			//$status = $this->_getTableStatus( $whatDatabase, $value );
			}
			
			$tables[] = array( 
				"aTable" => $t_value, "aKey" => $primaryKey, "aType" => "table", "aIcon" => "tableIcon", "aData" => $t_key, "aFields" => $fields 
			);
		}
		//sort ( $tables );
		

		return $tables;
	}

	/**
	 * I describe a table for the getDatabasesAndTables() method
	 *
	 * @param [string]  $database the database
	 * @param [string]  $table the table
	 * @return [array]
	 */
	public function _describeTable( $whatDatabase, $whatTable )
	{
		$fieldSQL = mysqli_query ( $this->dbconn, "SHOW FIELDS FROM $whatDatabase.$whatTable" );
		
		//loop all the results
		while ( $field = mysqli_fetch_assoc ( $fieldSQL ) )
		{
			$fields[] = $field;
		}
		return $fields;
	}

	/**
	 * I get the table status for a table only when called from getDatabases()
	 *
	 * @param [string] $whatDatabase
	 * @return [array]
	 */
	public function _getTableStatus( $whatDatabase )
	{
		$statusSQL = mysqli_query ( $this->dbconn, "SHOW TABLE STATUS FROM $whatDatabase" );
		$tableStatus = array();
		
		//loop all the results
		while ( $status = mysqli_fetch_assoc ( $statusSQL ) )
		{
			$tableStatus[] = $status;
		}
		return $tableStatus;
	}

	/**
	 * I get tables and fields 
	 *
	 * @param [string] $whatDatabase the database
	 * @return [array]
	 */
	public function _getTableAndFields( $whatDatabase )
	{
		$tableInfoSql = mysqli_query ( $this->dbconn, "SHOW TABLE STATUS FROM $whatDatabase" );
		
		$tables = array(); //array of all table info
		$fields = array(); //array of all field info
		

		//get the table name from the result
		while ( $tableInfo = mysqli_fetch_row ( $tableInfoSql ) )
		{
			$tables[] = $tableInfo[ 0 ];
			
			//loop threw every table inside of the tables array
			foreach ( $tables as $table )
			{
				//for each table, get the fields info for that table
				$fields = $this->_showFieldInfo ( $whatDatabase, $table );
			}
			$tableInfoAndFields[] = array( 
				'aTable' => $table, 'aType' => 'table', 'aFields' => $fields 
			);
		}
		$databaseInfoAndTables[] = array( 
			'aDatabase' => $whatDatabase, 'aType' => 'database', 'aTables' => $tableInfoAndFields 
		);
		
		//return $fields;
		//return $databaseInfoAndTables;
		return json_encode ( $databaseInfoAndTables );
	}

	public function _getDatabasesTablesAndFields()
	{
		$databaseInfoSql = mysqli_query ( $this->dbconn, "SHOW DATABASES" );
		
		$databases = array(); //array of all databases info
		$tables = array(); //array of all table info
		$fields = array(); //array of all field info
		

		//get the table name from the result
		while ( $databaseInfo = mysqli_fetch_row ( $databaseInfoSql ) )
		{
			
			$databases[] = $databaseInfo[ 0 ];
			//loop threw every table inside of the tables array
			foreach ( $databases as $database )
			{
				$tables = $this->_showTableInfo ( $database );
				//for each table, get the fields info for that table
				foreach ( $tables as $table )
				{
					$fields = $this->_showFieldInfo ( $database, $table );
				}
			}
			
			$tableInfoAndFields[] = array( 
				'aTable' => $table, 'aFields' => $fields 
			);
		}
		
		//return $fields;
		//return $tables;
		return json_encode ( $tableInfoAndFields );
	}

	/**
	 * I get information about the table.
	 *
	 * @param [string] $whatDatabase the database
	 * @return [array]
	 */
	public function _showTableInfo( $whatDatabase )
	{
		$tableInfoSql = "SHOW TABLE STATUS FROM $whatDatabase";
		$result = mysqli_query ( $this->dbconn, $tableInfoSql );
		$tableInfo = array();
		
		while ( $tables = mysqli_fetch_assoc ( $result ) )
		{
			$tableInfo[] = $tables;
		}
		return $tableInfo;
	}

	public function _showFieldInfo( $whatDatabase, $whatTable )
	{
		$fieldInfoSql = "SHOW FIELDS FROM $whatDatabase.$whatTable";
		$fieldInfo = array();
		
		$result = $this->dbconn->query ( $fieldInfoSql );
		
		while ( $fields = mysqli_fetch_assoc ( $result ) )
		{
			$fieldInfo[] = $fields;
		}
		return $fieldInfo;
	}

	/**
	 * I get the key for the table.
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [string]
	 */
	public function _getTableKey( $whatDatabase, $whatTable )
	{
		$indexInfoSql = "SHOW INDEX FROM $whatDatabase.$whatTable";
		$index = array();
		
		$result = $this->dbconn->query ( $indexInfoSql );
		
		while ( $indexes = mysqli_fetch_assoc ( $result ) )
		{
			if ( $indexes[ 'Key_name' ] == 'PRIMARY' )
			{
				$index = $indexes[ 'Column_name' ];
			}
		}
		return $index;
	}

	/**
	 * I ping mysql for a connection
	 *
	 * @return true or false
	 */
	public function ping()
	{
		$msg = '';
		/* check if server is alive */
		if ( $this->dbconn->ping () )
		{
			$msg = true;
		} else
		{
			$msg = false;
		}
		return $msg;
	}

	/* ********************************************************************
 * ********************************************************************
 *  
 *  			3. QUERY BUILDER/CREATE/UPDATE/DELETE METHODS
 * 
 * Below is all the methods for building insert queries, creating
 * databases, creating tables, creating users, removing data,
 * inserting data, and updating data.
 * Also there is methods for altering databases, and tables.
 * 
 * ********************************************************************
 * *********************************************************************/
	
	/**
	 * I build an insert query for the database.
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @param [array] $fieldValues array of Field/Values
	 * @return [string] SQL string
	 */
	public function buildQuery( $whatDatabase, $whatTable, $fieldValues, $whatMode )
	{
		require_once 'library/MySQLGen.php';
		$sqlGen = new MySQLGen ( $whatDatabase, $whatTable );
		
		//$key = $this->_getIndex( $whatDatabase, $whatTable );
		

		//For each loop, for each key => value in the array, setKeys( key, value ) for the generator to output the correct sql
		foreach ( $fieldValues as $key => $value )
		{
			//echo "$key => $value<br/>";
			$query = $sqlGen->setKeys ( $key, $value );
		}
		
		switch ( $whatMode )
		{
			case 'insert' :
				$query = $sqlGen->generateQuery ( 'insert' );
				return $query;
				//return $this->executeQuery ( $query );
				break;
			
			case 'update' :
				$query = $sqlGen->generateQuery ( 'update' );
				return $query;
				//return $this->executeQuery ( $query );
				break;
			
			case 'delete' :
				$query = $sqlGen->generateQuery ( 'delete' );
				return $query;
				//return $this->executeQuery ( $query );
				break;
		}
	
	}

	/**
	 * I create a database
	 *
	 * @param [string] $whatDatabase the name of the database
	 * @return [string] the result outcome
	 */
	public function createDatabase( $whatDatabase )
	{
		//CREATE DATABASE `tutorial_library` 
		//DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
		$sql = "CREATE SCHEMA IF NOT EXISTS $whatDatabase
				CHARACTER SET utf8";
		$result = mysqli_query ( $this->dbconn, $sql );
		
		if ( ! $result )
		{
			return 'There was an error creating the database.';
		}
		return 'Database created!';
	}

	/**
	 * I create a table
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the name of the new table
	 * @param [array] $fieldValues a assoc array of field/value pairs
	 * @return [string] the result
	 */
	public function createTable( $whatDatabase, $whatTable )
	{
		/**
		 * CREATE TABLE `books` ( id int ) DEFAULT CHARACTER SET latin1;
		 * 
		 */
		$sql = "CREATE TABLE $whatDatabase.$whatTable ( id int ) DEFAULT CHARACTER SET latin1";
		
		$result = mysqli_query ( $this->dbconn, $sql );
		
		if ( ! $result )
		{
			return false;
		}
		return true;
	}

	/**
	 * 
	 */
	public function alterTable( $whatDatabase, $whatTable, $whatQuery )
	{
		/**
		 *  ALTER TABLE `cars` ADD `engine` varchar(225) DEFAULT NULL ;
		 */
		$sql = "ALTER TABLE $whatDatabase.$whatTable ADD $whatQuery";
		
		$result = mysqli_query ( $this->dbconn, $sql );
		
		if ( ! $result )
		{
			return false;
		}
		return true;
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $whatDatabase
	 * @param unknown_type $whatTable
	 * @return unknown
	 */
	public function removeTable( $whatDatabase, $whatTable )
	{
		/**
		 *   DROP TABLE `library`;
		 */
		$sql = "DROP TABLE $whatDatabase.$whatTable";
		
		$result = mysqli_query ( $this->dbconn, $sql );
		
		if ( ! $result )
		{
			return false;
		}
		return true;
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $whatDatabase
	 * @param unknown_type $whatTable
	 * @param unknown_type $newName
	 * @return unknown
	 */
	public function renameTable( $whatDatabase, $whatTable, $newName )
	{
		/**
		 *   RENAME TABLE test.books TO test.the_books
		 */
		$sql = "RENAME TABLE $whatDatabase.$whatTable TO $newName";
		
		$result = mysqli_query ( $this->dbconn, $sql );
		
		if ( ! $result )
		{
			return false;
		}
		return true;
	}

	/**
	 * I insert data into the database
	 * INSERT INTO db.tbl SET name='value'
	 * 
	 * 
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @param [array] $whatQuery Array of Key/Value pairs for inserting in the database
	 * @return [string] the result
	 */
	public function insertRecord( $jsonQuery )
	{
		require_once 'JSONQuery.php';
		
		$j = new JSONQuery ( $this->dbconn );
		
		return $j->buildQuery ( $jsonQuery, 'INSERT' );
	}

	/**
	 * I update data from the database
	 * UPDATE db.tbl SET name='value'
	 * 
	 *
	 * @param [string] $jsonQuery Array of Key/Value pairs for updating the database
	 * @return [string] the result
	 */
	public function updateRecord( $jsonQuery )
	{
		require_once 'JSONQuery.php';
		
		$j = new JSONQuery ( $this->dbconn );
		
		return $j->buildQuery ( $jsonQuery, 'UPDATE' );
	}

	/**
	 * I remove data from the database
	 *
	 * @param [array] $jsonQuery the value to which remove by
	 * @return [string] the result
	 */
	public function removeRecord( $jsonQuery )
	{
		require_once 'JSONQuery.php';
		
		$j = new JSONQuery ( $this->dbconn );
		
		return $j->buildQuery ( $jsonQuery, 'DELETE' );
	}

	/* ********************************************************************
 * ********************************************************************
 *  
 * 	 				4. ANALYZE/CHECK/OPTIMIZE/REPAIR METHODS
 * 
 * Below is all the methods analyzing, checking, optimizing and repairing
 * database tables.
 * 
 * ********************************************************************
 * *********************************************************************/
	
	/**
	 * I analyze a table
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function analyzeTable( $whatDatabase, $whatTable )
	{
		$sql = "ANALYZE TABLE $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I check a table
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function checkTable( $whatDatabase, $whatTable )
	{
		$sql = "CHECK TABLE $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I optimize a table
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function optimizeTable( $whatDatabase, $whatTable )
	{
		$sql = "OPTIMIZE TABLE $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I repair a table
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function repairTable( $whatDatabase, $whatTable )
	{
		$sql = "REPAIR TABLE $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/* ********************************************************************
 * ********************************************************************
 *  
 * 	 					5. BACKUP/IMPORT/EXPORT METHODS
 * 
 * Below is all the methods for backing up the database, importing data,
 * exporting data.
 * 
 * ********************************************************************
 * *********************************************************************/
	/**
	 * I execute a query and return XML
	 *
	 * @param [string] $query the query 
	 * @return [xml]
	 */
	public function queryResultToXML( $query )
	{
		$xmlResult = '<?xml version="1.0"?>';
		
		/* Set the content type for the browser */
		
		//table query
		$sql = mysqli_query ( $this->dbconn, "$query" );
		
		$xmlResult .= "<results>";
		
		//loop all the results
		while ( $rows = mysqli_fetch_assoc ( $sql ) )
		{
			$xmlResult .= "<result>";
			//for each table in the result make an array
			foreach ( $rows as $key => $value )
			{
				$xmlResult .= "<$key>" . htmlspecialchars ( $value ) . "</$key>";
			}
			$xmlResult .= "</result>";
		}
		$xmlResult .= "</results>";
		
		return $xmlResult;
	}

	/**
	 * I execute a query and return JSON
	 *
	 * @param [string] $query the query
	 * @return [json]
	 */
	public function queryResultToJSON( $query )
	{
		return $this->_queryToJSON ( $query );
	}

	/**
	 * I execute a query and return json
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function exportToJSON( $whatDatabase, $whatTable )
	{
		$sql = "SELECRT * FROM $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I export the database and tables to XML
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [xml]
	 */
	public function exportToXML( $whatDatabase, $whatTable )
	{
		/* Set the content type for the browser */
		header ( "Content-type: text/xml" );
		
		//table query
		$sql = mysqli_query ( $this->dbconn, "SELECT * FROM $whatDatabase.$whatTable" );
		
		echo "<$whatDatabase>";
		
		//loop all the results
		while ( $rows = mysqli_fetch_assoc ( $sql ) )
		{
			echo "<$whatTable>";
			//for each table in the result make an array
			foreach ( $rows as $key => $value )
			{
				echo "<$key>" . htmlspecialchars ( $value ) . "</$key>";
			}
			echo "</$whatTable>";
		}
		echo "</$whatDatabase>";
	}

	/**
	 * I export data from the database
	 *
	 * @param [string] $whatDatabase
	 * @param [string] $whatTable
	 * @param [string all, db_structure, db_data, tbl_structure, tbl_data ] $whatMode 
	 * @return [string] the filename of the file.
	 */
	public function createBackup( $whatDatabase, $whatTable, $whatMode )
	{
		require_once 'MySQLDump.php';
		
		$result = '';
		$filename = $whatDatabase . '-' . $whatTable . '-' . $whatMode . '-' . $this->makeTimestamp () . '.sql';
		
		$dbDir = mkdir ( "backups/" . $whatDatabase );
		
		//Set the database, filename, and we don't want to use compression.
		$dumper = new MySQLDump ( $whatDatabase, "backups/$filename", false );
		$mode = $whatMode;
		
		//Switch based on what mode is specified
		switch ( $mode )
		{
			case 'all' :
				$dumper->doDump ();
				$result = 'Dumping all data';
				return true;
				break;
			
			case 'db_structure' :
				$dumper->getDatabaseStructure ();
				$result = 'Database structure backed up successfully.';
				$resultArray[] = array( 
					'mode' => $mode, 'result' => $result, 'filename' => $filename 
				);
				return $resultArray;
				break;
			
			case 'db_data' :
				$dumper->getDatabaseData ( false );
				$result = 'Database data backed up successfully.';
				$resultArray[] = array( 
					'mode' => $mode, 'result' => $result, 'filename' => $filename 
				);
				return $resultArray;
				break;
			
			case 'tbl_structure' :
				$dumper->getTableStructure ( $whatTable );
				$result = 'Table structure backed up successfully.';
				$resultArray[] = array( 
					'mode' => $mode, 'result' => $result, 'filename' => $filename 
				);
				return $resultArray;
				break;
			
			case 'tbl_data' :
				$dumper->getTableData ( $whatTable, false );
				$result = 'Table data backed up successfully.';
				$resultArray[] = array( 
					'mode' => $mode, 'result' => $result, 'filename' => $filename 
				);
				return $resultArray;
				break;
			
			default :
				$result = 'Please specify a mode.';
				$resultArray[] = array( 
					'mode' => $mode, 'result' => $result, 'filename' => $filename 
				);
				return $resultArray;
				break;
		}
		//return $result;
	}

	/**
	 * I get a list of all the backups in the backup folder
	 *
	 * @return [json]
	 */
	public function getDatabaseBackups( /*$whatDatabase*/ )
	{
		//$databaseDir = "$whatDatabase";
		$backupDir = "backups/"/* . $whatDatabase*/;
		$dir = opendir ( $backupDir );
		//		if ( ! $dir = opendir ( $backupDir ) )
		//		{
		//			$this->_makeDirectory ( $whatDatabase );
		//		}
		

		//loop the files and return the list
		while ( $file = readdir ( $dir ) )
		{
			$basepath = $backupDir . $file;
			
			$fileArray[] = array( 
				'aFilename' => $file, 'aSize' => filesize ( $basepath ), 'aCreated' => filemtime ( $basepath ), 'aType' => filetype ( $basepath ), //'aDatabase' => $whatDatabase, //'aCompressed' => 'false',
'aPermissions' => decoct ( fileperms ( $file ) ), 'isDir' => ( is_dir ( $file ) ) ? 'true' : 'false', 'isFile' => ( is_file ( $file ) ) ? 'true' : 'false', 'isReadable' => ( is_readable ( $file ) ) ? 'true' : 'false', 'isWritable' => ( is_writable ( $file ) ) ? 'true' : 'false' 
			);
		}
		closedir ( $dir );
		
		return json_encode ( $fileArray );
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $whatDatabase
	 * @param unknown_type $whatFile
	 * @return unknown
	 */
	public function removeBackup( $whatDatabase, $whatFile )
	{
		$removeFile = unlink ( "backups/$whatDatabase/$whatFile" );
		if ( $removeFile )
		{
			return $this->getDatabaseBackups ( $whatDatabase );
		}
		return 'There was a problem removing the backup.';
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $username
	 * @param unknown_type $password
	 * @param unknown_type $database
	 * @param unknown_type $destination
	 */
	public function backupDatabase( $username, $password, $database, $destination )
	{
		$sql = "SHOW TABLES FROM $database";
		$result = mysqli_query ( $this->dbconn, $sql );
		$table = '';
		while ( $row = mysqli_fetch_row ( $result ) )
		{
			$table = $row[ 0 ];
			$this->_backupTable ( $username, $password, $destination, $database );
			echo $row[ 0 ] . ' backed up<br/>';
		}
	
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $username
	 * @param unknown_type $password
	 * @param unknown_type $whatDestination
	 * @param unknown_type $whatDatabase
	 * @param unknown_type $whatTable
	 */
	private function _backupTable( $username, $password, $whatDestination, $whatDatabase, $whatTable = '' )
	{
		$filename = $this->_makeFileName ( $whatDestination, $whatDatabase, $whatTable );
		$cmd = "mysqldump -u $username -p $password -p $whatDatabase -tables > $filename";
		echo $cmd;
		system ( $cmd );
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $whatDestination
	 * @param unknown_type $whatDatabase
	 * @param unknown_type $whatTable
	 * @return unknown
	 */
	private function _makeFileName( $whatDestination, $whatDatabase, $whatTable = '' )
	{
		
		return ( ! empty ( $whatTable ) ) ? "{ $whatDestination }{ $whatDatabase  }@{ $whatTable }.sql" : "{ $whatDestination }{ $whatDatabase }.sql";
	
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $whatDirectory
	 * @return unknown
	 */
	private function _makeDirectory( $whatDirectory )
	{
		$base = "backups/";
		$dir = mkdir ( $base . $whatDirectory, 0700 );
		
		return $dir;
	
	}

	/* ********************************************************************
 * ********************************************************************
 * 
 * 						6. SERVER VARIABLES
 * 
 * Below is all the methods that build up information about the server
 * and system.
 * 
 * 
 * ********************************************************************
 * ********************************************************************/
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $whatThread
	 * @return unknown
	 */
	public function killProcess( $whatThread )
	{
		$sql = "KILL $whatThread";
		$message = '';
		if ( mysqli_query ( $this->dbconn, $sql ) )
		{
			$message = "Thread $whatThread was terminated.";
		} else
		{
			$message = "There was a problem terminating thread $whatThread.";
		}
		return $message;
	}

	/**
	 * I show all mysql system variables
	 *
	 * @return [json]
	 */
	public function showSystemVariables()
	{
		$sql = mysqli_query ( $this->dbconn, "SHOW VARIABLES" );
		$array = array();
		
		while ( $row = mysqli_fetch_assoc ( $sql ) )
		{
			$array[] = $row;
		}
		//$jsonTable = stripslashes( $array );
		$jsonTable = json_encode ( $array );
		
		//return $jsonTable;
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I show all system privileges
	 *
	 * @return [json]
	 */
	public function showSystemPrivileges()
	{
		$sql = "SHOW PRIVILEGES";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I show the system status
	 *
	 * @return [json]
	 */
	public function showSystemStatus()
	{
		$sql = "SHOW STATUS";
		$rows = mysqli_query ( $this->dbconn, $sql );
		$array = array();
		while ( $row = mysqli_fetch_assoc ( $rows ) )
		{
			//replace some of the names
			$row = str_replace ( 'Com_', '', $row );
			//take out the _ of the rows
			$row = str_replace ( '_', ' ', $row );
			
			$array[] = $row;
		}
		sort ( $row, SORT_ASC );
		return json_encode ( $array );
	}

	/**
	 * I show system processes
	 *
	 * @return [json]
	 */
	public function showSystemProcess()
	{
		$sql = "SHOW FULL PROCESSLIST";
		
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I show all of the systems users
	 *
	 * @return [json]
	 */
	public function showSystemUsers()
	{
		$sql = "SELECT * FROM mysql.user";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I get server info
	 *
	 * @return [json]
	 */
	public function _getServerInfo()
	{
		$serverArray = array();
		$aPath = $_SERVER[ 'DOCUMENT_ROOT' ];
		
		$serverArray[] = array( 
			'aDiskFreeSpace' => disk_free_space ( $aPath ), 'aDiskTotalSize' => disk_total_space ( $aPath ), 'aServerSoftware' => $_SERVER[ 'SERVER_SOFTWARE' ], 'aServerName' => $_SERVER[ 'SERVER_NAME' ], 'aPHPVersion' => PHP_VERSION, 'aPHPOs' => PHP_OS, 'aPHPExtensionDir' => PHP_EXTENSION_DIR, 'aMySQLClientV' => mysqli_get_client_info ( $this->dbconn ), 'aMySQLServerV' => mysqli_get_server_version ( $this->dbconn ), 'aMySQLHost' => mysqli_get_host_info ( $this->dbconn ), 'aMySQLProtocol' => mysqli_get_proto_info ( $this->dbconn ) 
		);
		
		return json_encode ( $serverArray );
	}

	/**
	 * I get all of the threads
	 *
	 * @return [json]
	 */
	public function _getThreads()
	{
		$sql = "SHOW STATUS LIKE '%Threads%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the temp size
	 *
	 * @return [json]
	 */
	public function _getTemp()
	{
		$sql = "SHOW STATUS LIKE '%tmp%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get open tables
	 *
	 * @return [json]
	 */
	public function _getOpen()
	{
		$sql = "SHOW STATUS LIKE '%Open%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the server uptime
	 *
	 * @return [json]
	 */
	public function _getUptime()
	{
		$sql = "show status like ''%uptime%'";
		$query = mysqli_query ( $this->dbconn, $sql );
		$array = array();
		
		while ( $row = mysqli_fetch_assoc ( $query ) )
		{
			echo $this->_formatUptime ( $row[ 'Value' ] );
		}
		
		//	return $array;
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the recent queries
	 *
	 * @return [json]
	 */
	public function _getQuestions()
	{
		$sql = "show status like 'Questions%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the query cache
	 *
	 * @return [json]
	 */
	public function _getQcache()
	{
		$sql = "SHOW STATUS LIKE '%Qcache%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get InnoDB
	 *
	 * @return [json]
	 */
	public function _getInnoDb()
	{
		$sql = "SHOW STATUS LIKE '%Innodb%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the key cache
	 *
	 * @return [json]
	 */
	public function _getKeys()
	{
		$sql = "SHOW STATUS LIKE '%Key%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the performance of mysql.
	 *
	 * @return [json]
	 */
	public function _getPerformance()
	{
		$sql = "SHOW STATUS LIKE '%Slow%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get all the sort
	 *
	 * @return [json]
	 */
	public function _getSort()
	{
		$sql = "SHOW STATUS LIKE '%Sort%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the connections
	 *
	 * @return [json]
	 */
	public function _getConnections()
	{
		$sql = "SHOW STATUS LIKE '%Connections%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the aborted clients and connections
	 *
	 * @return unknown
	 */
	public function _getClients()
	{
		$sql = "show status like 'Aborted%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get mysql bytes
	 *
	 * @return [json]
	 */
	public function _getBytes()
	{
		$sql = "SHOW STATUS LIKE '%Bytes%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get all the slave hosts
	 *
	 * @return [json]
	 */
	public function _getReplication()
	{
		$sql = "show status like '%Slave%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I get the commands
	 *
	 * @return [json]
	 */
	public function _getCommands()
	{
		$sql = "SHOW STATUS LIKE '%Com%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * I show all of the SHOW commands
	 *
	 * @return [json]
	 */
	public function _getShowCommands()
	{
		$sql = "SHOW STATUS LIKE '%Com_show%'";
		return $this->_queryStatusToJSON ( $sql );
	}

	/**
	 * Enter description here...
	 *
	 * @return unknown
	 */
	public function _getStat()
	{
		$stats = $this->dbconn->stat ();
		$newStats = explode ( ' ', $stats );
		
		return $newStats;
	}

	/* ********************************************************************
 * ********************************************************************
 * 
 * 						7. DATA METHODS
 * 
 * Below is all the methods for executing a query on the database, 
 * and getting all records from the database.
 * 
 * ********************************************************************
 * ********************************************************************/
	
	/**
	 * I get all the table data
	 *
	 * @param [string] $whatDatabase the database
	 * @param [string] $whatTable the table
	 * @return [json]
	 */
	public function getTableData( $whatDatabase, $whatTable )
	{
		$sql = "SELECT * FROM $whatDatabase.$whatTable";
		
		return $this->_queryToJSON ( $sql );
	}

	/**
	 * I execute a query
	 *
	 * @param [string] $query the query to execute
	 * @return [json]
	 */
	public function executeQuery( $query )
	{
		$sql = "$query";
		
		return $this->_queryToJSON ( $sql );
	}

	/* ********************************************************************
* ********************************************************************
* 
* 						8. RESULT HANDLERS
* 
* ********************************************************************
* ********************************************************************/
	
	/**
	 * I execute a query and return the results as json.
	 *
	 * @param [string] $sql the query to be executed
	 * @return [json] the result in json
	 */
	private function _queryToJSON( $sql )
	{
		$result = mysqli_query ( $this->dbconn, $sql );
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			$array[] = $row;
		}
		return json_encode ( $array );
	}

	/**
	 * I execute a query and return the result as an array.
	 *
	 * @param [string] $sql the query to be executed
	 * @return [array] the result array
	 */
	private function _queryToARRAY( $sql )
	{
		$query = mysqli_query ( $this->dbconn, $sql );
		$array = array();
		
		while ( $row = mysqli_fetch_assoc ( $query ) )
		{
			$array[] = $row;
		}
		
		return $array;
	}

	/**
	 * I get the query status
	 *
	 * @param unknown_type $sql
	 * @return unknown
	 */
	private function _queryStatusToJSON( $sql )
	{
		$result = mysqli_query ( $this->dbconn, $sql );
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			//replace some of the names
			$row = str_replace ( 'Com_', '', $row );
			//take out the _ of the rows
			$row = str_replace ( '_', ' ', $row );
			
			$array[] = $row;
		}
		sort ( $array );
		
		return json_encode ( $array );
	}

	/* ********************************************************************
* ********************************************************************
* 
* 						9. UTILITY METHODS
* 
* Below is all the utility methods for handling the results from a query
* and dumping variables or creating timestamps
* 
* 
* ********************************************************************
* ********************************************************************/
	
	/**
	 * I format debug dumps
	 *
	 * @param $var the variable you with to dump
	 */
	public function dumpIt( $var )
	{
		print "<pre>\n";
		print_r ( $var );
		print "</pre>\n";
	}

	/**
	 * I make a formatted timestamp.
	 * Ex. 2008-12-30 22:40:00
	 *
	 * @return [string] a timestamp
	 */
	private function makeTimestamp()
	{
		$time = time ();
		
		return date ( 'm-d-Y-H-i', $time );
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $time
	 * @return unknown
	 */
	private function _formatUptime( $time = 0 )
	{
		$days = ( int ) floor ( $time / 86400 );
		$hours = ( int ) floor ( $time / 3600 ) % 24;
		$minutes = ( int ) floor ( $time / 60 ) % 60;
		
		if ( $days == 1 )
		{
			$uptime = "$days day, ";
		} else if ( $days > 1 )
		{
			$uptime = "$days days, ";
		}
		if ( $hours == 1 )
		{
			$uptime .= "$hours hour";
		} else if ( $hours > 1 )
		{
			$uptime .= "$hours hours";
		}
		if ( $uptime && $minutes > 0 && $seconds > 0 )
		{
			$uptime .= ", ";
		} else if ( $uptime && $minutes > 0 & $seconds == 0 )
		{
			$uptime .= " and ";
		}
		( $minutes > 0 ) ? $uptime .= "$minutes minute" . ( ( $minutes > 1 ) ? "s" : NULL ) : NULL;
		
		return $uptime;
	}

	/**
	 * Enter description here...
	 *
	 * @param unknown_type $msg
	 * @param unknown_type $type
	 * @return unknown
	 */
	private function _throwError( $msg, $type )
	{
		switch ( $type )
		{
			case 'user' :
				throw ErrorException ();
				break;
			
			case 'error' :
				return trigger_error ( $msg, E_ERROR );
				break;
			
			case 'other' :
				return trigger_error ( $msg, E_USER_ERROR );
				break;
		}
		return trigger_error ( $msg, E_USER_ERROR );
	}

}

/* ********************************************************************
 * ********************************************************************
 * 
 * 						9. CLASS TESTING
 * 
 * Below is all the methods for testing all methods from this class.
 * 
 * ********************************************************************
 * ********************************************************************


$databaseName = 'sample';
$tableName = 'users';
$tableQuery = "SELECT * FROM $databaseName.$tableName LIMIT 5";

$svc = new MySQLService ( 'localhost', 'spratley_guest', 'guest' );


echo 'getTableColumns';
$svc->dumpIt( $svc->getTableColumns( $databaseName, $tableName ) );

echo 'showTableStatus';
$svc->dumpIt( $svc->showTableStatus( $databaseName ) );

echo 'getTableIndex';
$svc->dumpIt( $svc->getTableIndex( $databaseName, $tableName ) );

echo 'getDatabases';
$svc->dumpIt( $svc->getDatabases() );

echo 'getDatabasesAndTables';
$svc->dumpIt( $svc->getDatabasesAndTables() );

echo 'getTables';
$svc->dumpIt( $svc->getTables( $databaseName ) );

echo 'describeTable';
$svc->dumpIt( $svc->describeTable( $databaseName, $tableName ) );

echo '';
//$svc->dumpIt( $svc->getUserInfo( 'root' ) );

echo '_getTables';
$svc->dumpIt( $svc->_getTables( $databaseName ) );

echo '_describeTable';
$svc->dumpIt( $svc->_describeTable( $databaseName, $tableName ) );

echo '_getTableAndFields';
$svc->dumpIt( $svc->_getTableAndFields( $databaseName ) );

echo '_getDatabasesTablesAndFields';
$svc->dumpIt( $svc->_getDatabasesTablesAndFields() );

echo 'analyzeTable';
$svc->dumpIt( $svc->analyzeTable( $databaseName, $tableName ) );

echo 'checkTable';
$svc->dumpIt( $svc->checkTable( $databaseName, $tableName ) );

echo 'optimizeTable';
$svc->dumpIt( $svc->optimizeTable( $databaseName, $tableName ) );

echo 'repairTable';
$svc->dumpIt( $svc->repairTable( $databaseName, $tableName ) );

echo 'queryResultToJSON';
$svc->dumpIt( $svc->queryResultToJSON( $tableQuery ) );

echo 'queryResultToXML';
$svc->dumpIt( $svc->queryResultToXML( $tableQuery ) );

echo 'exportToJSON';
$svc->dumpIt( $svc->exportToJSON( $databaseName, $tableName ) );

echo 'exportToXML';
$svc->dumpIt( $svc->exportToXML( $databaseName, $tableName ) );

echo 'getDatabaseBackups';
$svc->dumpIt( $svc->getDatabaseBackups($databaseName ) );

echo '_makeFileName';
$svc->dumpIt( $svc->_makeFileName( 'backups', $databaseName, $tableName ) );

echo 'showSystemVariables';
$svc->dumpIt( $svc->showSystemVariables() );

echo 'showSystemUsers';
$svc->dumpIt( $svc->showSystemUsers() );

echo 'showSystemStatus';
$svc->dumpIt( $svc->showSystemStatus() );

echo 'showSystemProcess';
$svc->dumpIt( $svc->showSystemProcess() );

echo 'showSystemPrivileges';
$svc->dumpIt( $svc->showSystemPrivileges() );

echo '_getBytes';
$svc->dumpIt( $svc->_getBytes() );

echo '_getClients';
$svc->dumpIt( $svc->_getClients() );

echo '_getCommands';
$svc->dumpIt( $svc->_getCommands() );

echo '_getConnections';
$svc->dumpIt( $svc->_getConnections() );

echo '_getInnoDb';
$svc->dumpIt( $svc->_getInnoDb() );

echo '_getKeys';
$svc->dumpIt( $svc->_getKeys() );

echo '_getOpen';
$svc->dumpIt( $svc->_getOpen() );

echo '_getPerformance';
$svc->dumpIt( $svc->_getPerformance() );

echo '_getQcache';
$svc->dumpIt( $svc->_getQcache() );

echo '_getQuestions';
$svc->dumpIt( $svc->_getQuestions() );

echo '_getReplication';
$svc->dumpIt( $svc->_getReplication() );

echo '_getServerInfo';
$svc->dumpIt( $svc->_getServerInfo() );

echo '_getShowCommands';
$svc->dumpIt( $svc->_getShowCommands() );

echo '_getSort';
$svc->dumpIt( $svc->_getSort() );

echo '_getStat';
$svc->dumpIt( $svc->_getStat() );

echo '_getTableAndFields';
$svc->dumpIt( $svc->_getTableAndFields( $databaseName ) );

echo '_getTables';
$svc->dumpIt( $svc->_getTables( $databaseName ) );

echo '_getTemp';
$svc->dumpIt( $svc->_getTemp() );

echo '_getThreads';
$svc->dumpIt( $svc->_getThreads() );






*/

?>